﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuNhapHangDAO
    {
        public List<PhieuNhapHangDTO> LayDanhSachPhieuNhapHang()
        {
            List<PhieuNhapHangDTO> ds = new List<PhieuNhapHangDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayDanhSachPhieuNhapHang";
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                PhieuNhapHangDTO dto = new PhieuNhapHangDTO();
                dto.MaPhieuNhap = dr.GetInt32(0);
                dto.NgayNhap = dr.GetDateTime(1);
                dto.MaNhaCungCap = dr.GetInt32(2);
                dto.NhanVienNhap = dr.GetInt32(3);
                dto.TongTien = dr.GetDouble(4);
                dto.GhiChu = dr.GetString(5);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        //new
        //public List<PhieuNhapHangDTO> LayDanhSachPhieuNhapHangTheoNgay(DateTime tungay, DateTime denngay)
        public List<PhieuNhapHangDTO> LayDanhSachPhieuNhapHangTheoNgay(string tungay, string denngay)
        {
            List<PhieuNhapHangDTO> ds = new List<PhieuNhapHangDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayDanhSachPhieuNhapHangTheoNgay";
            //cm.Parameters.Add("@TuNgay", SqlDbType.DateTime).Value = tungay;
            //cm.Parameters.Add("@DenNgay", SqlDbType.DateTime).Value = denngay;
            cm.Parameters.Add("@TuNgay", SqlDbType.VarChar).Value = tungay;
            cm.Parameters.Add("@DenNgay", SqlDbType.VarChar).Value = denngay;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                PhieuNhapHangDTO dto = new PhieuNhapHangDTO();
                dto.MaPhieuNhap = dr.GetInt32(0);
                dto.NgayNhap = dr.GetDateTime(1);
                dto.MaNhaCungCap = dr.GetInt32(2);
                dto.NhanVienNhap = dr.GetInt32(3);
                dto.TongTien = dr.GetDouble(4);
                dto.GhiChu = dr.GetString(5);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public PhieuNhapHangDTO LayThongTinPhieuNhapHang(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayThongTinPhieuNhapHang";
            cm.Parameters.Add("@MaPhieuNhap", SqlDbType.Int).Value = ma;
            SqlDataReader dr = cm.ExecuteReader();
            PhieuNhapHangDTO dto = new PhieuNhapHangDTO();
            dr.Read();
            dto.MaPhieuNhap = dr.GetInt32(0);
            dto.NgayNhap = dr.GetDateTime(1);
            dto.MaNhaCungCap = dr.GetInt32(2);
            dto.NhanVienNhap = dr.GetInt32(3);
            dto.TongTien = dr.GetDouble(4);
            dto.GhiChu = dr.GetString(5);
            dr.Close();
            conn.Close();
            return dto;
        }

        public int PhatSinhMaPhieuNhap()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_PhatSinhMaPhieuNhap";
            int ma = (int)cm.ExecuteScalar();
            return ma;
        }

        public void ThemPhieuNhapHang(PhieuNhapHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_ThemPhieuNhapHang";
            cm.Parameters.Add("@MaPhieuNhap", SqlDbType.Int).Value = dto.MaPhieuNhap;
            cm.Parameters.Add("@NgayNhap", SqlDbType.DateTime).Value = dto.NgayNhap;
            cm.Parameters.Add("@MaNhaCungCap", SqlDbType.Int).Value = dto.MaNhaCungCap;
            cm.Parameters.Add("@NhanVienNhap", SqlDbType.NVarChar).Value = dto.NhanVienNhap;
            cm.Parameters.Add("@TongTien", SqlDbType.Int).Value = dto.TongTien;
            cm.Parameters.Add("@GhiChu", SqlDbType.NVarChar).Value = dto.GhiChu;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void XoaPhieuNhapHang(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_XoaPhieuNhapHang";
            cm.Parameters.Add("@MaPhieuNhap", SqlDbType.Int).Value = ma;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatPhieuNhapHang(PhieuNhapHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_CapNhatPhieuNhapHang";
            cm.Parameters.Add("@MaPhieuNhap", SqlDbType.Int).Value = dto.MaPhieuNhap;
            cm.Parameters.Add("@NgayNhap", SqlDbType.DateTime).Value = dto.NgayNhap;
            cm.Parameters.Add("@MaNhaCungCap", SqlDbType.Int).Value = dto.MaNhaCungCap;
            cm.Parameters.Add("@NhanVienNhap", SqlDbType.NVarChar).Value = dto.NhanVienNhap;
            cm.Parameters.Add("@TongTien", SqlDbType.Int).Value = dto.TongTien;
            cm.Parameters.Add("@GhiChu", SqlDbType.NVarChar).Value = dto.GhiChu;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        //public float TongTienTatCaPhieuNhapHang()
        //{
        //    SqlConnection conn = DataProvider.ConnectDB();
        //    SqlCommand cm = new SqlCommand();
        //    cm.Connection = conn;
        //    cm.CommandType = CommandType.StoredProcedure;
        //    cm.CommandText = "usp_TongTienTatCaPhieuNhapHang";
        //    float t = (float)cm.ExecuteScalar();
        //    return t;
        //}
    }
}
